/**
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.api.adwords.starterkit.db.tables;

import com.google.api.adwords.starterkit.db.keys.TextAdKey;
import com.google.api.adwords.starterkit.db.structures.TextAdStructure;
import com.google.api.adwords.starterkit.utils.DumpExceptions;
import com.google.api.adwords.v201101.cm.AdGroupAdStatus;
import com.google.api.adwords.v201101.cm.UserStatus;

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;

/**
 * The Class TextAdUtils encapsulates most of TextAd request to DB.
 * 
 * @author rodrigo.f.vale@gmail.com  (Rodrigo Vale)
 */
public class TextAdTable {
  private PreparedStatement prepNew;
  private PreparedStatement prepUpdate;
  private PreparedStatement prepLoad;
  private PreparedStatement prepTouched;
  private PreparedStatement prepBatchUpdated;
  private PreparedStatement prepExists;
  private PreparedStatement prepSetId;
  private Connection conn;
  private PreparedStatement prepResetIsBanned;
  private PreparedStatement prepResetIsUpdated;
  private PreparedStatement prepResetIsTouched;
  private static final Logger log = Logger.getLogger("StarterKit");

  /**
   * Instantiates a new text ad utils.
   *
   * @param conn the conn
   */
  public TextAdTable(Connection conn) {
    this.conn = conn;
    try {
      Statement stat = conn.createStatement();

      stat.executeUpdate(
          "CREATE TABLE IF NOT EXISTS TextAds "
        + "  ( "
        + "     LocalId         INTEGER PRIMARY KEY, "
        + "     CreativeId      LONG DEFAULT 0, "
        + "     LocalAdGroupId  LONG DEFAULT 0, "
        + "     AdGroupId       LONG DEFAULT 0, "
        + "     HeadLine        STRING DEFAULT '', "
        + "     Description1    STRING DEFAULT '', "
        + "     Description2    STRING DEFAULT '', "
        + "     DisplayUrl      STRING DEFAULT '', "
        + "     DestinationUrl  STRING DEFAULT '', "
        + "     IsTouched         BOOLEAN DEFAULT 0, "
        + "     IsStatsAvailable        BOOLEAN DEFAULT 0, "
        + "     IsUpdated         BOOLEAN DEFAULT 0, "
        + "     IsStatusModifiable BOOLEAN DEFAULT 1, "
        + "     Ctr             FLOAT DEFAULT 0, "
        + "     Clicks          INTEGER DEFAULT 0, "
        + "     Cost            FLOAT DEFAULT 0, "
        + "     Impressions     INTEGER DEFAULT 0, "
        + "     Conversions     INTEGER DEFAULT 0, "
        + "     Transactions    INTEGER DEFAULT 0, "
        + "     Status          STRING DEFAULT 0, "
        + "     IsBanned          BOOLEAN DEFAULT 0, "
        + "     ApiErrorMsg        STRING DEFAULT '', "
        + "     ApiErrorTrigger    STRING DEFAULT ''"
        + "  ); ");      
      stat.executeUpdate(
          "CREATE INDEX IF NOT EXISTS TextAdsLocalAdGroupIdIndex on "
        + "TextAds (LocalAdGroupId);");
      stat.executeUpdate(
          "CREATE INDEX IF NOT EXISTS TextAdsCreativeIdIndex on " 
        + "TextAds (CreativeId);");      
      
      prepExists = conn.prepareStatement(
          "SELECT HeadLine FROM TextAds WHERE LocalAdGroupId = ? " 
        + "AND HeadLine = ? AND Description1 = ? AND Description2 = ?;");
      prepLoad = conn.prepareStatement(
          "SELECT * FROM TextAds WHERE LocalAdGroupId = ? AND HeadLine = ? " 
        + "AND Description1 = ? AND Description2 = ?;");
      
      prepTouched = conn.prepareStatement(
          "UPDATE TextAds SET IsTouched = ? WHERE LocalAdGroupId = ? " 
        + "AND HeadLine = ? AND Description1 = ? AND Description2 = ?;"); 
      prepBatchUpdated = conn.prepareStatement(
          "UPDATE TextAds SET IsUpdated = ?, IsTouched = ?, ApiErrorMsg = ?, "
        + "ApiErrorTrigger = ? " 
        + "WHERE LocalAdGroupId = ? AND HeadLine = ? AND Description1 = ? " 
        + "AND Description2 = ?;");
      prepSetId = conn.prepareStatement(
          "UPDATE TextAds SET CreativeId = ?, IsUpdated = ?, IsTouched = ? " 
        + "WHERE LocalAdGroupId = ? AND HeadLine = ? AND Description1 = ? " 
        + "AND Description2 = ?;");
      
      prepNew = conn.prepareStatement(
          "REPLACE INTO TextAds (CreativeId, LocalAdGroupId, AdGroupId, " 
        + "HeadLine, Description1, Description2, DisplayUrl, DestinationUrl, " 
        + "IsStatsAvailable, IsUpdated, IsStatusModifiable, Ctr, Clicks, Cost, " 
        + "Impressions, Conversions, Transactions, Status, IsBanned, " 
        + "IsTouched) " 
        + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " 
        + "?, ?);"); 
      prepUpdate = conn.prepareStatement(
          "REPLACE INTO TextAds (LocalId, CreativeId, LocalAdGroupId, " +
          "AdGroupId, HeadLine, Description1, Description2, DisplayUrl, " +
          "DestinationUrl, IsStatsAvailable, IsUpdated, IsStatusModifiable, " +
          "Ctr, Clicks, Cost, Impressions, Conversions, Transactions, " +
          "Status, IsBanned, IsTouched) " +
          "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
          "?, ?, ?, ?, ?);");    
      prepResetIsBanned = conn.prepareStatement(
          "UPDATE TextAds SET IsBanned = ?");
      prepResetIsUpdated = conn.prepareStatement(
          "UPDATE TextAds SET IsUpdated = ?");
      prepResetIsTouched = conn.prepareStatement(
          "UPDATE TextAds SET IsTouched = ?");
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
      System.exit(1);
    }    
  }

  /**
   * Checks if a TextAd Exists.
   *
   * @param txtAdKey the textad key
   * @return true, if successful
   */
  public boolean exists(TextAdKey txtAdKey) {
    boolean hasKey = false;
    ResultSet rs = null;
    try {
      prepExists.setInt(1, txtAdKey.getLocalAdGroupId());      
      prepExists.setString(2, txtAdKey.getHeadline());
      prepExists.setString(3, txtAdKey.getDescription1());
      prepExists.setString(4, txtAdKey.getDescription2());
      rs = prepExists.executeQuery();
      if (rs.next()) {
        hasKey = true;
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return hasKey;
  }

  /**
   * Gets the TextAd Structure of a generic SQL query.
   *
   * @param rs the Resultset to be converted to a TextAd Strucutre
   * @return the TextAd Structure 
   */
  public TextAdStructure getStructure(ResultSet rs) {
    TextAdStructure txtAdStructure = new TextAdStructure();
    try {
      txtAdStructure.setLocalId(rs.getInt("LocalId"));
      txtAdStructure.setCreativeId(rs.getLong("CreativeId"));
      txtAdStructure.localAdGroupId = rs.getInt("LocalAdGroupId");
      txtAdStructure.setAdGroupId(rs.getLong("AdGroupId"));
      txtAdStructure.setHeadline(rs.getString("HeadLine"));
      txtAdStructure.setDescription1(rs.getString("Description1")); 
      txtAdStructure.setDescription2(rs.getString("Description2"));
      txtAdStructure.setDisplayUrl(rs.getString("DisplayUrl"));
      txtAdStructure.setDestinationUrl(rs.getString("DestinationUrl"));
      txtAdStructure.setCtr(rs.getFloat("Ctr"));
      txtAdStructure.setClicks(rs.getLong("Clicks"));
      txtAdStructure.setCost(rs.getFloat("Cost"));
      txtAdStructure.setImpressions(rs.getLong("Impressions"));
      txtAdStructure.setConversions(rs.getLong("Conversions"));
      txtAdStructure.setTransactions(rs.getLong("Transactions"));
      txtAdStructure.setStatus(rs.getString("Status").toUpperCase());
      txtAdStructure.setBanned(rs.getBoolean("IsBanned"));      
      txtAdStructure.setStatusModifiable(rs.getBoolean("IsStatusModifiable"));
      txtAdStructure.setStatsAvailable(rs.getBoolean("IsStatsAvailable"));
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return txtAdStructure;
  }

  /**
   * Load a TextAd from DB.
   *
   * @param txtAdKey the textAd primary key
   * @return the textad structure
   */
  public TextAdStructure get(TextAdKey txtAdKey) {
    TextAdStructure textAdStructure = null;
    ResultSet rs = null;
    try {
      prepLoad.setInt(1, txtAdKey.getLocalAdGroupId());
      prepLoad.setString(2, txtAdKey.getHeadline());
      prepLoad.setString(3, txtAdKey.getDescription1());
      prepLoad.setString(4, txtAdKey.getDescription2());
      rs = prepLoad.executeQuery();
      if (rs.next()) {
        textAdStructure = getStructure(rs);
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return textAdStructure;
  }

  /**
   * Save a TextAd Structure into DB.
   *
   * @param txtAdStructure the txt ad structure
   * @param updated the updated
   * @param touched the touched
   * @return the text ad structure
   */
  public TextAdStructure save(TextAdStructure txtAdStructure, boolean updated, 
      boolean touched) {
    ResultSet rs = null;
    try {
      PreparedStatement prep;
      int count = 1;
      if (txtAdStructure.getLocalId() == 0) {
        prep = prepNew;  
      } else {
        prep = prepUpdate; 
        prep.setLong(count++, txtAdStructure.getLocalId());
      }
      prep.setLong(count++, txtAdStructure.getCreativeId());
      prep.setInt(count++, txtAdStructure.localAdGroupId);
      prep.setLong(count++, txtAdStructure.getAdGroupId());       
      prep.setString(count++, txtAdStructure.getHeadline());
      prep.setString(count++, txtAdStructure.getDescription1());
      prep.setString(count++, txtAdStructure.getDescription2());
      prep.setString(count++, txtAdStructure.getDisplayUrl());
      prep.setString(count++, txtAdStructure.getDestinationUrl());
      prep.setBoolean(count++, txtAdStructure.isStatsAvailable());
      prep.setBoolean(count++, updated);
      prep.setBoolean(count++, txtAdStructure.getCanModifyStatus());
      prep.setFloat(count++, txtAdStructure.getCtr());
      prep.setLong(count++, txtAdStructure.getClicks());
      prep.setFloat(count++, txtAdStructure.getCost());
      prep.setLong(count++, txtAdStructure.getImpressions());
      prep.setLong(count++, txtAdStructure.getConversions());
      prep.setLong(count++, txtAdStructure.getTransactions());
      prep.setString(count++, 
          txtAdStructure.getStatus().getValue().toUpperCase());
      prep.setBoolean(count++, txtAdStructure.isBanned());
      prep.setBoolean(count++, touched);
      prep.executeUpdate();
      
      rs = prep.getGeneratedKeys();      
      txtAdStructure.setLocalId(rs.getInt(1));    
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return txtAdStructure;
  }
  
  /**
   * Execute all batch operations in the pipeline.
   */
  public void executeBatch() {
    try {
      prepNew.executeBatch();
      prepUpdate.executeBatch();
      prepSetId.executeBatch();
      prepBatchUpdated.executeBatch();    
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }    
  }
  
  /**
   * Add a TextAd update into the batch operation pipeline.
   *
   * @param txtAdStructure the TextAd structure
   * @param updated the updated flag
   * @param touched the touched flag
   */
  public void addToBatch(TextAdStructure txtAdStructure, boolean updated, 
      boolean touched) {
    try {
      PreparedStatement prep;
      int count = 1;
      if (txtAdStructure.getLocalId() == 0) {
        prep = prepNew; 
      } else {
        prep = prepUpdate; 
        prep.setLong(count++, txtAdStructure.getLocalId());
      }
      prep.setLong(count++, txtAdStructure.getCreativeId());
      prep.setInt(count++, txtAdStructure.localAdGroupId);
      prep.setLong(count++, txtAdStructure.getAdGroupId());       
      prep.setString(count++, txtAdStructure.getHeadline());
      prep.setString(count++, txtAdStructure.getDescription1());
      prep.setString(count++, txtAdStructure.getDescription2());
      prep.setString(count++, txtAdStructure.getDisplayUrl());
      prep.setString(count++, txtAdStructure.getDestinationUrl());
      prep.setBoolean(count++, txtAdStructure.isStatsAvailable());
      prep.setBoolean(count++, updated);
      prep.setBoolean(count++, txtAdStructure.getCanModifyStatus());
      prep.setFloat(count++, txtAdStructure.getCtr());
      prep.setLong(count++, txtAdStructure.getClicks());
      prep.setFloat(count++, txtAdStructure.getCost());
      prep.setLong(count++, txtAdStructure.getImpressions());
      prep.setLong(count++, txtAdStructure.getConversions());
      prep.setLong(count++, txtAdStructure.getTransactions());
      prep.setString(count++, 
          txtAdStructure.getStatus().getValue().toUpperCase());
      prep.setBoolean(count++, txtAdStructure.isBanned());
      prep.setBoolean(count++, touched);
      prep.addBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }  

  /**
   * Sets the touched flag.
   *
   * @param txtAdKey the TextAd primary key
   * @param touched the touched flag
   */
  public void setTouched(TextAdKey txtAdKey, boolean touched) {
    try {
      prepTouched.setBoolean(1, touched);
      prepTouched.setInt(2, txtAdKey.getLocalAdGroupId());
      prepTouched.setString(3, txtAdKey.getHeadline());
      prepTouched.setString(4, txtAdKey.getDescription1());
      prepTouched.setString(5, txtAdKey.getDescription2());
      prepTouched.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }  

  /**
   * Sets the updated flag.
   *
   * @param txtAdKey the TextAd primary key
   * @param updated the updated flag
   */
  public void setUpdated(TextAdKey txtAdKey, boolean updated) {
    try {
      prepUpdate.setBoolean(1, updated);
      prepUpdate.setInt(2, txtAdKey.getLocalAdGroupId());
      prepUpdate.setString(3, txtAdKey.getHeadline());
      prepUpdate.setString(4, txtAdKey.getDescription1());
      prepUpdate.setString(5, txtAdKey.getDescription2());
      prepUpdate.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }  

  /**
   * Sets the batch updated.
   *
   * @param txtAdKey the TextAd key
   * @param updated the updated flag
   * @param touched the touched flag
   */
  public void setBatchUpdated(TextAdKey txtAdKey, boolean updated, 
      boolean touched, String error, String trigger) {
    try {
      prepBatchUpdated.setBoolean(1, updated);
      prepBatchUpdated.setBoolean(2, touched);
      prepBatchUpdated.setString(3, error);
      prepBatchUpdated.setString(4, trigger);
      prepBatchUpdated.setInt(5, txtAdKey.getLocalAdGroupId());
      prepBatchUpdated.setString(6, txtAdKey.getHeadline());
      prepBatchUpdated.setString(7, txtAdKey.getDescription1());
      prepBatchUpdated.setString(8, txtAdKey.getDescription2());
      prepBatchUpdated.addBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }
  
  /**
   * Sets the creative id based on a batch process.
   *
   * @param txtAdKey the txt ad key
   * @param creativeId the creative id
   * @param updated the updated
   * @param touched the touched
   */
  public void setBatchId(TextAdKey txtAdKey, long creativeId, boolean updated, 
      boolean touched) {
    try {
      prepSetId.setLong(1, creativeId);
      prepSetId.setBoolean(2, updated);
      prepSetId.setBoolean(3, touched);
      prepSetId.setInt(4, txtAdKey.getLocalAdGroupId());
      prepSetId.setString(5, txtAdKey.getHeadline());
      prepSetId.setString(6, txtAdKey.getDescription1());
      prepSetId.setString(7, txtAdKey.getDescription2());
      prepSetId.addBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }  

  /**
   * Reset all flags to its default values
   */
  public void reset() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement("UPDATE TextAds SET " +
          "IsUpdated = 0, IsTouched = 0, IsStatusModifiable = 1;");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }        
  }

  /**
   * Update ad groups ids at keyword table.
   */
  public void updateAdGroupsIds() {
    log.debug("Updating AdGroupId on new TextAds");
    HashMap<Integer, Long> adGroupsIdMap = getNewAdGroupsIds();
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE TextAds SET AdGroupId = ? " 
          + "WHERE LocalAdGroupId = ? AND AdGroupId = 0");
      int count = 0;
      for (int localAdGroupId : adGroupsIdMap.keySet()) {
        long adGroupId = adGroupsIdMap.get(localAdGroupId);
        prep.setLong(1, adGroupId);
        prep.setLong(2, localAdGroupId);
        prep.addBatch();
        count++;
        if (count == 100000) {
          count = 0;
          prep.executeBatch();
        }
      }
      prep.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }      
  }

  /**
   * Gets the adgroup ids map.
   *
   * @return the ad group ids map
   */
  public HashMap<Integer, Long> getNewAdGroupsIds() {
    log.debug("Loading AdGroups Ids needed by new TextAds");
    HashMap<Integer, Long> adGroupIdsMap = new HashMap<Integer, Long>(); 
    ResultSet rs = null;
    try {
      Statement stat = conn.createStatement();
      rs = stat.executeQuery(
            "SELECT ag.LocalId, ag.AdGroupId FROM AdGroups ag, TextAds ta "
          + "WHERE ta.LocalAdGroupId = ag.LocalId and ta.AdGroupId = 0");
      while (rs.next()) {
        adGroupIdsMap.put(rs.getInt("LocalId"), rs.getLong("AdGroupId"));
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return adGroupIdsMap;
  }
  
  /**
   * Return all TextAds modified during the last Starter Kit execution
   * 
   * @return the ResultSet that contains TextAds modified 
   */
  public ResultSet getModified() {
    PreparedStatement prep = null;
    ResultSet rs = null;
    try {
      prep = conn.prepareStatement(
          "SELECT * FROM TextAds WHERE IsUpdated = 1 AND IsBanned = 0");
      rs = prep.executeQuery();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return rs;
  }

  /**
   * Resets IsBanned flag in all rows of AdGroups table
   */
  public void resetIsBanned(boolean isBanned) {
    try {
      prepResetIsBanned.setBoolean(1, isBanned);
      prepResetIsBanned.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }     
  }

  /**
   * Resets IsUpdated flag in all rows of AdGroups table
   */
  public void resetIsUpdated(boolean isUpdated) {
    try {
      prepResetIsUpdated.setBoolean(1, isUpdated);
      prepResetIsUpdated.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }     
  }

  /**
   * Resets IsUpdated flag in all rows of AdGroups table
   */
  public void resetIsTouched(boolean isTouched) {
    try {
      prepResetIsTouched.setBoolean(1, isTouched);
      prepResetIsTouched.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }     
  }

  /**
   * Set the Status to PAUSED for all TextAds not touched by the 
   * application.
   */
  public void autoPause() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "UPDATE TextAds SET Status = ? WHERE isTouched = 0 " +
          "AND Status NOT IN (?, ?)");
      prep.setString(1, UserStatus.PAUSED.getValue());
      prep.setString(2, UserStatus.PAUSED.getValue());
      prep.setString(3, UserStatus.DELETED.getValue());
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
  }

  /**
   * Grant that all banned TextAd will not be updated by StarterKit
   * synchronization process.
   */
  public void grantBanned() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "UPDATE TextAds SET IsUpdated = 0, IsTouched = 0 " +
          "WHERE IsBanned = 1");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }      
  }

  /**
   * Reset all campaigns status flags to its default values
   */
  public void resetStatus() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE TextAds SET IsUpdated = 0, IsTouched = 0");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
  }
  
  public HashSet<TextAdStructure> getList(HashSet<Integer> localIds) {
    PreparedStatement prep = null;
    HashSet<TextAdStructure> textAds = new HashSet<TextAdStructure>();
    try {
      prep = conn.prepareStatement(
            "SELECT * FROM TextAds WHERE LocalId = ?");
      Iterator <Integer> i = localIds.iterator();
      while (i.hasNext()) {
        int localId = i.next();
        prep.setInt(1, localId);
        ResultSet rs = prep.executeQuery();
        TextAdStructure textAdStructure = getStructure(rs);
        textAds.add(textAdStructure);
        rs.close();
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }         
    return textAds;
  }
}
